package com.rookie.tool.module.util.sql;

import org.springframework.util.StringUtils;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 文本转创建表sql
 * 形如 【phone	手机号】 格式,换行符不能丢
 *
 * @author rookie
 */
public class TextToSqlUtils {
    private static List<MySqlTable.SqlField> formatText(String text) {
        List<MySqlTable.SqlField> fields = new LinkedList<>();
        if (!StringUtils.hasText(text)) {
            return new ArrayList<>();
        }
        String[] split = text.split("\n");
        for (String s : split) {
//            去除左右空格
            s = s.trim();
            String[] field = s.split("\\s+");
            MySqlTable.SqlField sqlField = new MySqlTable.SqlField();
            if (field.length == 2) {
                sqlField.setFieldName(field[0]);
                sqlField.setFieldType(field[1]);
            } else if (field.length == 3) {
                sqlField.setFieldName(field[0]);
                sqlField.setComment(field[2]);
                sqlField.setFieldType(field[1]);
            } else if (field.length > 3){
                sqlField.setFieldName(field[0]);
                sqlField.setComment(arrayToString(field));
                sqlField.setFieldType(field[1]);
            }else{
                continue;
            }
            fields.add(sqlField);
        }

        return fields;
    }

    /**
     * @param text 格式
     * @return 结果
     */
    public static String appendSql(String text, String tableName, String tableNickname) {
        List<MySqlTable.SqlField> sql = formatText(text);
        StringBuilder stringSql = new StringBuilder();
        stringSql.append(String.format("CREATE TABLE `%s` (\n", tableName));
        String pattern = "`%s` %s NULL DEFAULT NULL COMMENT '%s',";
        String endString = "`%s` %s NULL DEFAULT NULL COMMENT '%s'";
        for (int i = 0; i < sql.size(); i++) {
            if (i != sql.size() - 1) {
                stringSql.append(String.format(pattern, sql.get(i).getFieldName(),
                        sql.get(i).getFieldType(),
                        sql.get(i).getComment()))
                        .append("\n");
            } else {
                stringSql.append(String.format(endString, sql.get(i).getFieldName(),
                        sql.get(i).getFieldType(),
                        sql.get(i).getComment()))
                        .append("\n");
            }

        }

        stringSql.append(String.format(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='%s';", tableNickname));
        return stringSql.toString();
    }

    public static void main(String[] args) throws IOException {
        StringBuilder text = new StringBuilder();
        File file = new File("E:\\table.txt");
        FileInputStream fr = new FileInputStream(file);
        //控制读进来的数据的编码格式
        InputStreamReader isr = new InputStreamReader(fr, StandardCharsets.UTF_8);
        BufferedReader br = new BufferedReader(isr);
        String str = null;
        String tableName = "";
        String tableNickname = "";
        int i = 0;
        //一次读一行，如果文件内容读完返回null
        while ((str = br.readLine()) != null) {
            i++;
            if (!isColumn(str) && (str.contains("（") || str.contains("("))) {
                if (i != 1) {
                    System.out.println(appendSql(text.toString(), tableName, tableNickname));
                    System.out.println("\n\n");
//                清空
                    text.setLength(0);
                }
                if (str.contains("（")) {
                    tableNickname = str.substring(0, str.indexOf("（"));
                    tableName = str.substring(str.indexOf("（") + 1, str.length() - 1);
                } else if (str.contains("(")) {
                    tableNickname = str.substring(0, str.indexOf("("));
                    tableName = str.substring(str.indexOf("(") + 1, str.length() - 1);
                } else {
                    System.out.println("表读取错误");
                    return;
                }
            } else if ("-----".equals(str.trim())) {

            } else {
                text.append(str).append("\n");
            }
        }
        System.out.println(appendSql(text.toString(), tableName, tableNickname));
    }

    private static boolean isColumn(String str) {
        str = str.trim();
        if (str.length() < 1) {
            return false;
        }
        return str.charAt(0) < 'z' && str.charAt(0) > 'A';
    }

    private static String arrayToString(String[] arr) {
        StringBuilder result = new StringBuilder();
        for (int i = 2; i < arr.length; i++) {
            result.append(arr[i]);
        }
        return result.toString();
    }

    private static boolean matchLetter(String str) {
        String regex=".*[a-zA-Z]+.*";
        Matcher m= Pattern.compile(regex).matcher(str);
        return m.matches();
    }
}
